package task.nms;

import java.lang.reflect.Constructor;
import java.lang.reflect.Modifier;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;

import task.DbTask.Traverser;
import task.gui.DbUtil;
import util.JdbcUtil;
import util.Pair;

public class SanityTest extends NmsTask
{
	private String schema;


	public SanityTest() {
    }
    abstract class Test {
        protected StringBuilder sb = new StringBuilder();
        public final boolean test() throws SQLException {
            doTest();
            return sb.length() == 0;
        }
        public abstract void doTest() throws SQLException;
        public final String getResult() {
            return sb.toString();
        }
    }
    
    public class SequenceTest extends Test {

		@Override
		public void doTest() throws SQLException {
			
		}
    	
    }
    public class ConnectionTemplateTest extends Test {

		@Override
		public void doTest() throws SQLException {
			String sql = "select id_pk, disc_settings FROM core_discover_settings";
			final ArrayList<Pair<Integer, String>> secAttrList = new ArrayList<Pair<Integer,String>>();
			new Traverser(sql) {
				@Override
				protected void visit(ResultSet rs) throws SQLException {
					String discSettings = rs.getString(2);
					int from = discSettings.indexOf("SecurityAttributes=");
					if (from<0) return;
					from = discSettings.indexOf("[", from) + 1;
					int to = discSettings.indexOf("]", from);
					String secAttrs = discSettings.substring(from, to);
					secAttrs = secAttrs.replaceAll("/", ",");
					int id = rs.getInt(1);
					secAttrList.add(new Pair<Integer, String>(id, secAttrs));
				}
			};
			for (Pair<Integer, String> e : secAttrList) {
				int n = e.getB().split(",").length;
				String sql2 = String.format("select count(*) from state_me_security_template where id_pk in (%s)", e.getB());
				long numRows = JdbcUtil.getSingleLong(conn(), sql2);
				if (numRows < n) {
					sb.append(String.format("coreDiscSetting with idPk=%s " +
							"has %s security attribute id%s that does not exist in statMeSecTempl%n", e.getA(), n-numRows, n-numRows>1?"s":""));
				}
			}
		}
    }
    public class NeDomainTest extends Test {
        @Override
        public void doTest() throws SQLException
        {
        	String sql = String.format("select ID_PK, USER_LABEL from %s.STATE_COMMON_RESOURCE " +
        			"where OBJECT_TYPE=1 and UNMANAGED_TIME is null and id_pk in " +
        			"(select COMMON_RESOURCE_FK from %s.STATE_RESOURCES_IN_DOMAINS " +
        			"where DOMAIN_FK in (select id_pk from %s.STATE_COMMON_RESOURCE where UNMANAGED_TIME is not null))", schema, schema, schema);
        	final StringBuilder sb2 = new StringBuilder();
        	new Traverser(sql) {
				@Override
				protected void visit(ResultSet rs) throws SQLException {
					sb2.append(String.format("  id_pk:%5s '%s'%n", rs.getString(1), rs.getString(2)));
				}
        	};
        	if (sb2.length()>0) {
        		sb.append("These managed NEs have an unmanaged Domain:\n").append(sb2);
        	}
        }
    }
    public class StateTest extends Test {
        @Override
        public void doTest() throws SQLException
        {
            String[] stateTables = {"state_administrative_domains","state_ems", "state_equipment", "state_equipment_holders", "state_managed_elements", "state_pmps"}; 
            Long[] stateCounts = new Long[stateTables.length];
            long total = 0;
            for (int i=0 ; i<stateCounts.length ; i++) {
                stateCounts[i] = JdbcUtil.getSingleLong(conn(), String.format("select count(*) from %s.%s", schema, stateTables[i]));
                total += stateCounts[i];
            }
            Long commResrCnt = JdbcUtil.getSingleLong(conn(), String.format("select count(*) from %s.state_common_resource", schema));
            Long commResrRegCnt = JdbcUtil.getSingleLong(conn(), String.format("select count(*) from %s.state_common_resource_registry", schema));
            if (commResrCnt.longValue() != commResrRegCnt.longValue()) {
                sb.append(String.format("#state_common_resource:%s differ from #state_common_resource_registry:%s%n", commResrCnt, commResrRegCnt));
            }
            if (commResrCnt != total) {
                sb.append(String.format("#state_common_resource:%s differ from sum(#state_*):%s%n", commResrCnt, total));
            }
            Long union = union("state_common_resource", "ID_PK", "state_common_resource_registry", "COMMON_RESOURCE_FK");
            if (union != total) {
                sb.append("Different sets: {state_common_resource.ID_PK} and {state_common_resource_registry.COMMON_RESOURCE_FK}\n");
            }
            for (int i=0 ; i<stateTables.length-1 ; i++) {
                for (int j=i+1 ; j<stateTables.length ; j++) {
                    Long cnt = union(stateTables[i], "ID_PK", stateTables[j], "ID_PK");
                    if (cnt > 0) {
                        sb.append(String.format("Tables %s and %s have %s rows with same ID_PK", stateTables[i], stateTables[j], cnt));
                    }
                }
            }
            for (int i=0 ; i<stateTables.length ; i++) {
                Long cnt = union("state_common_resource", "ID_PK", stateTables[i], "ID_PK");
                if (cnt.longValue() != stateCounts[i].longValue()) {
                    sb.append(String.format("%s rows in %s does not have corresponding row in state_common_resource%n", stateCounts[i].longValue() - cnt, stateTables[i]));
                }
            }
        }

        private Long union(String t1, String key1, String t2, String key2) throws SQLException
        {
            String sql = String.format("select count(*) from %s.%s where %s in (select %s from %s.%s)", schema, t1, key1, key2, schema, t2);
            Long result = JdbcUtil.getSingleLong(conn(), sql);
            return result;
        }
    }
    public class CountTest extends Test {
        @Override
        public void doTest() throws SQLException
        {
            String count1 = JdbcUtil.getSingleString(conn(), String.format("select count(*) from %s.MED_DISCOVERED_ELEMENTS where INCLUDE_STATE=1", schema));
            String count2 = JdbcUtil.getSingleString(conn(), String.format("select count(*) from %s.STATE_MANAGED_ELEMENTS", schema));
            if (!count1.equals(count2)) {
                sb.append(String.format("#(MED_DISCOVERED_ELEMENTS(INCLUDE_STATE=1)):%s differ from #(STATE_MANAGED_ELEMENTS):%s%n", count1, count2));
            }
        }
    }
    public class ManagedTest extends Test {
        @Override
        public void doTest() throws SQLException
        {
            String sql = String.format("select me.SD_NAME from %s.STATE_MANAGED_ELEMENTS me, %s.STATE_COMMON_RESOURCE cr, %s.MED_DISCOVERED_ELEMENTS de " +
            		"where me.ID_PK=cr.ID_PK and cr.UNMANAGED_TIME is not null " +
            		"and de.NAME=me.SD_NAME and de.INCLUDE_STATE=%s", schema, schema, schema, getTrue());
            Statement stmt = conn().createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            String prefix = "These NE has COMMUNICATION_STATE=1 but UNMANAGED_TIME is NOT NULL\n\t";
            while (rs.next()) {
                sb.append(prefix).append(rs.getString(1)).append('\n');
                prefix = "\t";
            }
            rs.close();
            stmt.close();
        }
    }
    public class ManagedDiscoveredTest extends Test {
        @Override
        public void doTest() throws SQLException
        {
            String sql = String.format("select SD_NAME from %s.STATE_MANAGED_ELEMENTS where SD_NAME not in (select NAME from %s.MED_DISCOVERED_ELEMENTS)", schema, schema);
            Statement stmt = conn().createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            String prefix = "These NE exists in STATE_MANAGED_ELEMENTS but not in MED_DISCOVERED_ELEMENTS\n\t";
            while (rs.next()) {
                sb.append(prefix).append(rs.getString(1)).append('\n');
                prefix = "\t";
            }
            rs.close();
            stmt.close();
        }
    }
    public class SnmpVersionInConnTemplateTest extends Test {
        @Override
        public void doTest() throws SQLException
        {
        	String[] versions = { "1","2"};
        	String[] names = {"'SmartConnect','SmartNodeC','SmartNodeA'," +
        			"'WiLink_SA','NetLink-III-AU','NetLink-II-AU','NetLink-I-GU'," +
        			"'CompactLink','NetLink-I-AU','WiLinkITDDSA','WiLinkITDDBS'," +
        			"'WiLinkXL_AP','WiLink','SmartMetro'",
        			"'CityLinkFE','FlexLink','EvolutionAccess','Evolution','SmartPack','BG-20','BG-30'"};
        	for (int i=0 ; i<names.length ; i++) {
        		String sql = String.format("select count(*) from %s.MED_DISCOVERED_ELEMENTS " +
        				"where SNMP_VERSION<>'%s' and PRODUCT_NAME in (%s)", schema, versions[i], names[i]);
        		long count = JdbcUtil.getSingleLong(conn(), sql);
        		if (count>0) {
        			sb.append(count).append(" discovered elements have wrong snmp version\n");
        		}
        	}
        }
    }
    public boolean test() throws SQLException {
        boolean result = true;
        Class<?>[] typeArgs = new Class<?>[] {SanityTest.class};
        for (Class<?> testCls : SanityTest.class.getDeclaredClasses()) {
            if (testCls.isInterface()) continue;
            if ((testCls.getModifiers() & Modifier.ABSTRACT) != 0) continue;
            if (!Test.class.isAssignableFrom(testCls)) continue;
            try
            {
                Constructor<?> con = testCls.getConstructor(typeArgs);
                Test test = (Test) con.newInstance(this);
                if (!test.test()) {
                    result = false;
                    System.out.printf("%s failed: %s%n", testCls.getSimpleName(), test.getResult());
                } else {
                    System.out.printf("%s succeded%n", testCls.getSimpleName());
                }
            }
            catch (Exception e)
            {
                e.printStackTrace();
            }
        }
        return result;
    }
    
    



	/**
     * @param args
     * @throws ClassNotFoundException 
     * @throws SQLException 
     */
    public static void main(String[] args) throws SQLException, ClassNotFoundException
    {
        Connection conn = JdbcUtil.getConnection("jdbc:oracle:thin:@nmsora:1521:nmstest", "fmj_a", "fmj");
        SanityTest test = new SanityTest();
        test.setConnection(conn);
        test.test();
    }


	@Override
	public void doRun(Map<String, String> propMap) throws SQLException {
		schema = propMap.get("schema");
		test();
	}


	@Override
	public String getDescription() {
		String descr = "Test the consistency of the NetMaster DB";
		return descr;
	}
	@Override
	public String[] propertyNames() {
		String[] props = { "schema" };
		return props;
	}
}
